﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OneMake.Model
{
    //各客服绩效情况
    public class report_kf_scope
    {
        [MapToField(FieldName = "日期")]
        public DateTime stat_datetime { get; set; }

        [MapToField(FieldName = "旺旺昵称")]
        public string ww { get; set; }

        [MapToField(FieldName = "接待人数")]
        public int n_serve { get; set; }

        [MapToField(FieldName = "询单人数")]
        public int n_inquire { get; set; }

        [MapToField(FieldName = "付款人数")]
        public int n_paid { get; set; }

        [MapToField(FieldName = "付款金额")]
        public double n_paid_amount { get; set; }

        [MapToField(FieldName = "询单人均产值")]
        public double avg_paid_amount { get; set; }

        [MapToField(FieldName = "平均响应时间（秒）")]
        public double avg_wwanswer { get; set; }

        [MapToField(FieldName = "旺旺回复率")]
        public double per_wwreply { get; set; }

        [MapToField(FieldName = "客单价（元）")]
        public double paid_uprice { get; set; }

        [MapToField(FieldName = "客件数（件）")]
        public double n_paid_prds { get; set; }

        [MapToField(FieldName = "下单成功率（%）")]
        public double per_todaypaid { get; set; }

        [MapToField(FieldName = "付款成功率（%）")]
        public double per_finalpaid { get; set; }

        [MapToField(FieldName = "完成退款件数（件）")]
        public double n_done_amount { get; set; }

        [MapToField(FieldName = "完成退款金额（元）")]
        public double n_done_money { get; set; }
    }

    partial class ReportHelper
    {
        private T _get_avg_kf_scope<T>(PetaPoco.Database db, string tablename, string fieldName, string ww, int shopId, DateTime day)
        {
            string sql = string.Format(@"select round(avg({0}),2) from {1} where shopid=@0 and stat_datetime=@1 AND {0}<>0 AND ww='{0}'", fieldName, tablename, fieldName, ww);
            return db.SafeExecuteScalar<T>(sql, shopId, day.ToShortDateString());
        }
        private T _get_sum_kf_scope<T>(PetaPoco.Database db, string tablename, string fieldName, string ww, int shopId, DateTime day)
        {
            string sql = string.Format(@"select sum({0}) from {1} where shopid=@0 and stat_datetime=@1 AND ww='{0}'", fieldName, tablename, ww);
            return db.SafeExecuteScalar<T>(sql, shopId, day.ToShortDateString());
        }
        public List<report_kf_scope> get_report_kf_scope(int shopid, DateTime day)
        {
            var db = new PetaPoco.Database("mysql" + UserSession.Network);
            List<report_kf_scope> reports = new List<report_kf_scope>();

            string sql = "select stat_datetime,ww,sum(n_serve) as n_serve, sum(n_inquire) as n_inquire, sum(n_paid) as n_paid, sum(n_paid_amount) as n_paid_amount from ct_d_jxdb where shopid=@0 and stat_datetime=@1 group by ww";
            foreach (report_kf_scope report in db.Query<report_kf_scope>(sql, shopid, day.Date))
            {

                if (report.n_inquire != 0)
                {
                    report.avg_paid_amount = Math.Round(report.n_paid_amount / report.n_inquire, 2);
                }
                else
                {
                    report.avg_paid_amount = 0;
                }

                report.avg_wwanswer = _get_avg_kf_scope<double>(db, "ct_d_gzldb", "avg_wwanswer", report.ww, shopid, day);
                report.per_wwreply = _get_avg_kf_scope<double>(db, "ct_d_gzldb", "per_wwreply", report.ww, shopid, day);
                report.paid_uprice = _get_avg_kf_scope<double>(db, "ct_d_kdjdb", "paid_uprice", report.ww, shopid, day);
                report.n_paid_prds = _get_avg_kf_scope<double>(db, "ct_d_kjsdb", "n_paid_prds", report.ww, shopid, day);
                report.per_todaypaid = _get_avg_kf_scope<double>(db, "ct_d_cgldb", "per_todaypaid", report.ww, shopid, day);
                report.per_finalpaid = _get_avg_kf_scope<double>(db, "ct_d_cgldb", "per_finalpaid", report.ww, shopid, day);

                report.n_done_amount = _get_sum_kf_scope<double>(db, "ct_d_tkqkdb", "n_done_amount", report.ww, shopid, day);
                report.n_done_money = _get_sum_kf_scope<double>(db, "ct_d_tkqkdb", "n_done_money", report.ww, shopid, day);

                reports.Add(report);
            }
            return reports;
        }

        public List<report_kf_scope> get_uc_report_kf_scopes(int shopid, DateTime beginTime, DateTime endTime)
        {
            System.Diagnostics.Debug.Assert(beginTime <= endTime);

            List<report_kf_scope> reports = new List<report_kf_scope>();
            DateTime tempTime = beginTime;
            do
            {
                reports.AddRange(get_report_kf_scope(shopid, tempTime));
                tempTime = tempTime.AddDays(1);
            }
            while (tempTime <= endTime);

            return reports;
        }
    }
}
